LeetCode SQL Questions

SQL query to find the Nth highest salary:

For example, given the above Employee table,
the nth highest salary where N = 2 is 200.
 If there is no Nth highest salary, then the query 
should return null. You can see that we have used 
the above query to find the highest, second-highest, 
and third-highest salaries from the employee table.

Below we will use classic Employee and Department tables:

1. SQL query to show Employee (names) who have a bigger salary than their manager?

In this problem, you need to compare employees' salaries to their manager's salary. To achieve this, you need two instances of the same table. Also in order to find a Manager you need to compare employee id with manager id, this is achieved by using the self-join in SQL, where two instances of the same table are compared.

-- Employees (names) who have a bigger salary than their manager

SELECT a.emp_name FROM Employee a JOIN Employee b
ON a.mngr_id = b.emp_id
WHERE a.salary > b.salary;

You can see that Admin 2, and UNIX has higher salary than their boss, CEO who just earn $100. They key here is use of self join, if you have to compare data from the same table then you can create two instance of same table and join them together using self join. An interesting technique to solve this kind of SQL query problem.

2. SQL query to find Employees who have the biggest salary in their Department?

This is a little bit complex problem to solve, you first need to find the maximum salary of each department, but the department doesn't have the salary, it is the employee who has the salary. 

So we need to create a virtual or temp table where we should have both department and salary. 

This can be achieved by joining both Employee and Department table on dept_id and then using GROUP by clause to group salary on dept_id.  Now, someone can question why we didn't use the self join?

Since we need to print the name of the employee who has the highest salary, we need to compare each employee's salary with the department's highest salary which we have just calculated. 

This can be done by keeping the result of the previous query in a temp table and then joining it again with the Employee table. 

-- Employees who have the biggest salary in their Department
SELECT a.emp_name, a.dept_id
FROM Employee a JOIN
(SELECT a.dept_id, MAX(salary) as max_salary
FROM Employee a JOIN Department b ON a.dept_id = b.dept_id
GROUP BY a.dept_id) b
ON a.salary = b.max_salary AND a.dept_id = b.dept_id;

You can see that CEO, Adm 2, Legal 1, and UNIX has highest salary in their 
respective department. You can also print department name as an additional 
exercise. 

3.SQL query to list Departments that have less than 3 people in it?

This is a rather simple SQL query interview question to solve.
You just need to know how to use the COUNT() function and GROUP BY clause.

-- Departments that have less than 3 people in it
SELECT dept_id, COUNT(emp_name) as 'Number of Employee'
FROM Employee
GROUP BY dept_id
HAVING COUNT(emp_name) < 3;

Output:

4. SQL query to show all Departments along with the number of people there?

This is a tricky problem, candidates often use inner join to solve the problem, leaving out empty departments.

-- All Department along with the number of people there
SELECT b.dept_name, COUNT(a.dept_id) as 'Number of Employee'
FROM Employee a FULL OUTER JOIN Department b ON a.dept_id=b.dept_id
GROUP BY b.dept_name;

Output

5.SQL query to show all Employees that don't have a manager in the same department?

This is similar to the first SQL query interview question, where we have used self-join to solve the problem. There we compared the salary of employee and here we have compared their department.

-- Employees that don't have a manager in the same department
SELECT a.emp_name FROM Employee a JOIN Employee b
ON a.mngr_id = b.emp_id
WHERE a.dept_id != b.dept_id;

Output

6.Can you write SQL query to list all Departments along with the total salary of that department?

This problem is similar to the 4th question in this list. Here also you need to use OUTER JOIN instead of INNER join to include empty departments which should have no salaries.

-- All Department along with the total salary there
SELECT b.dept_name, SUM(a.salary) as 'Total Salary'
FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id
GROUP BY b.dept_name;

Output: